package com.zretc.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import com.zretc.dao.SongDao;
import com.zretc.entity.Area;
import com.zretc.entity.Comment;
import com.zretc.entity.Singer;
import com.zretc.entity.Song;
import com.zretc.entity.SongType;
import com.zretc.util.DBUtil;
import com.zretc.util.PageInfo;

/**
 * Song的dao实现类
 * @author Administrator
 *
 */
public class SongDaoImpl implements SongDao {
	
	/**
	 * 根据歌曲类型筛选，按条件排序
	 * @author Administrator
	 * return PageInfo<Song>
	 */
	@Override
	public PageInfo<Song> findSongByConditionAndOrder(Map<String, String> map) {
		String sql = "";
		List<Song> list = new ArrayList<Song>();
		ResultSet rs = null;
		Song song = null;
		SongType songType = null;
		Singer singer = null;
		Area area = null;
		PageInfo<Song> pageInfo = null;
		Integer pageNum = Integer.parseInt(map.get("pageNum"));
		Integer pageSize = Integer.parseInt(map.get("pageSize"));
		Integer total = 0;
		
		//传入判断条件(点击量、上传时间或地区)进行筛选
		if ("".equals(map.get("rankingCondition"))) {
			if ("orderByClickCount".equals(map.get("order"))) {
				sql="select A.song_id,A.song_name,A.song_time,A.click_count,A.song_status,C.type_id,B.singer_id,A.song_text,A.publish_date,"
						+ "	B.singer_name,B.gender,B.singer_picture,C.type_name,D.area_id,D.area_name"
						+ " from song A left join singer B on A.singer_id = B.singer_id left join songtype C on A.type_id = C.type_id "
						+ "	left join area D on B.area_id = D.area_id"
						+ " order by A.click_count desc limit ?,?";
				rs = DBUtil.doQuery(sql,(pageNum-1)*pageSize,pageSize);
				
			}else if ("orderByPublishDate".equals(map.get("order"))) {
				sql="select A.song_id,A.song_name,A.song_time,A.click_count,A.song_status,C.type_id,B.singer_id,A.song_text,A.publish_date,"
						+ "	B.singer_name,B.gender,B.singer_picture,C.type_name,D.area_id,D.area_name"
						+ " from song A left join singer B on A.singer_id = B.singer_id left join songtype C on A.type_id = C.type_id "
						+ "	left join area D on B.area_id = D.area_id"
						+ " order by A.publish_date asc limit ?,?";
				rs = DBUtil.doQuery(sql,(pageNum-1)*pageSize,pageSize);
			}
		}else {
				sql="select A.song_id,A.song_name,A.song_time,A.click_count,A.song_status,C.type_id,B.singer_id,A.song_text,A.publish_date,"
						+ "	B.singer_name,B.gender,B.singer_picture,C.type_name,D.area_id,D.area_name"
						+ " from song A left join singer B on A.singer_id = B.singer_id left join songtype C on A.type_id = C.type_id "
						+ "	left join area D  on B.area_id = D.area_id"
						+ " where D.area_name like ? order by A.click_count desc limit ?,?";
				rs = DBUtil.doQuery(sql,"%"+map.get("rankingCondition")+"%",(pageNum-1)*pageSize,pageSize);
		}
		
		try {
			while (rs.next()) {
				Integer songId = rs.getInt("song_id");
				String songName = rs.getString("song_name");
				String songTime = rs.getString("song_time");
				String publishDate = rs.getString("publish_date");
				Integer clickCount = rs.getInt("click_count");
				Integer songStatus = rs.getInt("song_status");
				Integer typeId = rs.getInt("type_id");
				Integer singerId = rs.getInt("singer_id");
				String songText = rs.getString("song_text");
				String singerName = rs.getString("singer_name");
				String gender = rs.getString("gender");
				String singerPicture = rs.getString("singer_picture");
				String typeName = rs.getString("type_name");
				Integer areaId = rs.getInt("area_id");
				String areaName = rs.getString("area_name");
				songType = new SongType(typeId, typeName);
				area = new Area(areaId, areaName);
				singer = new Singer(singerId, singerName, gender, singerPicture, area);
				song = new Song(songId, songName, songTime, clickCount, songStatus, songType, singer, songText, publishDate);
				list.add(song);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		
		rs = DBUtil.doQuery("select count(*),A.song_id,A.song_name,A.song_time,A.click_count,A.song_status,C.type_id,B.singer_id,A.song_text,A.publish_date,"
				+ "	B.singer_name,B.gender,B.singer_picture,C.type_name,D.area_id,D.area_name"
				+ " from song A left join singer B on A.singer_id = B.singer_id left join songtype C on A.type_id = C.type_id "
				+ "	left join area D  on B.area_id = D.area_id"
				+ " where D.area_name like ?", "%"+map.get("rankingCondition")+"%");
		try {
			while (rs.next()) {
				total = rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		pageInfo = new PageInfo<>(list, pageNum, pageSize, total);
		
		return pageInfo;
		
	}

	@Override
	public List<Song> findSongBySongName(String songName) {
		List<Song> list = new ArrayList<Song>();
		Song song = null;
		SongType songType = null;
		Singer singer = null;
		Area area = null;
		ResultSet rs = DBUtil.doQuery("select song_id,song_name,song_time,click_count,song_text,song_status,type_id,singer_id,publish_date from song where song_name = ?", songName);
		try {
			while (rs.next()) {
				Integer songId = rs.getInt("song_id");
				String songTime = rs.getString("song_time");
				String publishDate = rs.getString("publish_date");
				Integer clickCount = rs.getInt("click_count");
				Integer songStatus = rs.getInt("song_status");
				Integer typeId = rs.getInt("type_id");
				Integer singerId = rs.getInt("singer_id");
				String songText = rs.getString("song_text");
				songType = new SongType(typeId, null);
				singer = new Singer(singerId, null, null, null, area);
				song = new Song(songId, songName, songTime, clickCount, songStatus, songType, singer, songText, publishDate);
				list.add(song);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return list;
	}

	@Override
	public Song findSongBySongId(Integer songId) {
		Song song = null;
		SongType songType = null;
		Singer singer = null;
		Area area = null;
		ResultSet rs = DBUtil.doQuery("select A.song_id,A.song_name,A.song_time,A.click_count,A.song_text,A.song_status,B.type_id,B.type_name,"
									+ " C.singer_id,C.singer_name,C.gender,C.singer_picture,D.area_id,D.area_name,A.publish_date "
									+ " from song A left join songtype B on A.type_id = B.type_id "
									+ " left join singer C on A.singer_id = C.singer_id "
									+ " left join area D on C.area_id = D.area_id where A.song_id = ?", songId);
		try {
			while (rs.next()) {
				String songName = rs.getString("song_name");
				String songTime = rs.getString("song_time");
				String publishDate = rs.getString("publish_date");
				Integer clickCount = rs.getInt("click_count");
				Integer songStatus = rs.getInt("song_status");
				Integer typeId = rs.getInt("type_id");
				String typeName = rs.getString("type_name");
				Integer singerId = rs.getInt("singer_id");
				String singerName = rs.getString("singer_name");
				String gender = rs.getString("gender");
				String singerPicture = rs.getString("singer_picture");
				Integer areaId = rs.getInt("area_id");
				String areaName = rs.getString("area_name");
				String songText = rs.getString("song_text");
				songType = new SongType(typeId, typeName);
				area = new Area(areaId, areaName);
				singer = new Singer(singerId, singerName, gender, singerPicture, area);
				song = new Song(songId, songName, songTime, clickCount, songStatus, songType, singer, songText, publishDate);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return song;
	}

}
